package com.ficus.road.maintain.util.excel;

import cn.hutool.core.util.ReflectUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.ficus.road.maintain.core.exception.BadRequestException;
import com.ficus.road.maintain.core.model.smartnew.annotation.Excel;
import com.ficus.road.maintain.dto.ChecklistOfCar;
import com.ficus.road.maintain.dto.PushStatistic;
import com.ficus.road.maintain.util.FormatUtil;
import com.google.common.base.Objects;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.*;
import java.lang.reflect.Method;

/**
 * @author xiawen yang
 * @date 2021/11/19 下午2:58
 */
@Slf4j
public class ExcelUtil {

    private static final double DEFAULT_NUMERIC_VALUE = 0d;
    private static final String DEFAULT_STRING_VALUE = "";

    private static List<String> getCellHead(Class clazz) {
        Field[] fields = clazz.getDeclaredFields();
        List<String> res = null;
        if (ObjectUtils.isNotEmpty(fields)) {
            res = new ArrayList<>(fields.length);
        }
        for (int i = 0; i < fields.length; i++) {
            if (fields[i].isAnnotationPresent(ExcelProperty.class)) {
                ExcelProperty excelProperty = fields[i].getAnnotation(ExcelProperty.class);
                String[] values = excelProperty.value();
                res.add(values[0]);
            }
        }
        return res;
    }

//    public static String[] getFieldsName(Class clazz) {
//        clazz
//    }

    public static <T> void exportCollections2Excel(String dirPath, String filePath, T object, String fieldName, int sheetNo) {
        Workbook workbook = exportCollectionData(object, fieldName, dirPath, filePath, sheetNo);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(filePath);
            workbook.write(fos);
            fos.flush();
        } catch (IOException e) {
            log.error("输出excel时发生错误", e);
        } finally {
            try {
                if (null != fos) {
                    fos.close();
                }
                if (null != workbook) {
                    workbook.close();
                }
            } catch (IOException e) {
                log.error("关闭输出流时发生错误", e);
            }
        }
    }

    public static <T> String exportObject2Excel(String dirPath, String filePath, T data, int sheetNo) {
        Workbook workbook = exportObjectData(data, dirPath, filePath, sheetNo);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(filePath);
            workbook.write(fos);
            fos.flush();
        } catch (IOException e) {
            log.error("输出excel时发生错误", e);
        } finally {
            try {
                if (null != fos) {
                    fos.close();
                }
                if (null != workbook) {
                    workbook.close();
                }
            } catch (IOException e) {
                log.error("关闭输出流时发生错误", e);
            }
        }
        return filePath;
    }

    private static <T> Workbook exportCollectionData(T object, String fieldName, String dirPath, String filePath, int sheetNo) {
        Class actualClazz = null;
        //这里没法用泛型了
        List data = null;
        try {
            Class<?> clazz = object.getClass();
            Field field = clazz.getDeclaredField(fieldName);
            Type genericType = field.getGenericType();
            ParameterizedType pt = (ParameterizedType) genericType;
            String rawTypeName = pt.getRawType().getTypeName();
            if (!"java.util.List".equals(rawTypeName)) {
                throw new UnsupportedOperationException("导入类型非java.util.List类型！");
            }
            field.setAccessible(true);
            data = (List) field.get(object);
            String actualTypeName = pt.getActualTypeArguments()[0].getTypeName();
            actualClazz = Class.forName(actualTypeName);
        } catch (NoSuchFieldException | ClassNotFoundException | IllegalAccessException e) {
            e.printStackTrace();
        }
        FileInputStream fis = null;
        Workbook workbook = null;
        try {
            //创建文件夹
            File dir = new File(dirPath);
            if (!dir.exists()) {
                dir.mkdirs();
                dir.setWritable(true, false);
            }
            //创建文件
            File file = new File(filePath);
            if (!file.exists()) {
                file.createNewFile();
            }
            //每次写入到新的文件
            workbook = new SXSSFWorkbook();
            Sheet sheet = buildDataSheet(workbook, actualClazz, sheetNo);
            int rowNum = 1, mergeRowStart, mergeRowEnd = 0;
            int numberOfSheets = workbook.getNumberOfSheets();
            mergeRowStart = rowNum;
            for (Iterator it = data.iterator(); it.hasNext(); ) {
                Object next = it.next();
                if (next == null) {
                    continue;
                }
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(workbook, next, row);
            }
            mergeRowEnd = rowNum - 1;
            //合并单元格操作
            CellRangeAddress rangeAddress = new CellRangeAddress(mergeRowStart, mergeRowEnd, 0, 0);
            sheet.addMergedRegion(rangeAddress);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != fis) {
                    fis.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return workbook;
    }

    private static <T> Workbook exportObjectData(T data, String dirPath, String filePath, int sheetNo) {
        FileInputStream fis = null;
        Workbook workbook = null;
        try {
            File dir = new File(dirPath);
            if (!dir.exists()) {
                dir.setWritable(true, false);
                dir.mkdirs();
            }
            File file = new File(filePath);
            if (!file.exists()) {
                file.createNewFile();
            }
            if (file.length() == 0) {
                workbook = new SXSSFWorkbook();
            } else {
                // 从原来的excel中读取出workbook，这样创建sheet后可以达到追加sheet的效果
                fis = new FileInputStream(filePath);
                // 当file文件为空时，调用这个方法报错
                workbook = new XSSFWorkbook(fis);
            }
            //拿出第一张sheet
            Sheet sheet = workbook.getSheetAt(0);
            int rowNum = 0, mergeRowStart, mergeRowEnd = 0;
            sheet = createCellHead(data.getClass(), sheet, sheet.getLastRowNum() + 1);
            rowNum = sheet.getLastRowNum() + 1;
            Row row = sheet.createRow(rowNum);
            convertDataToRow(workbook, data, row);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != fis) {
                    fis.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return workbook;
    }

    /**
     * 生成sheet表，并写入第一行数据（列头）
     *
     * @param workbook 工作簿对象
     * @return 已经写入列头的Sheet
     */
    private static Sheet buildDataSheet(Workbook workbook, Class clazz, int sheetNo) {
        Sheet sheet = workbook.createSheet(String.valueOf(sheetNo));
        // 刚创建的sheet都在第0行创建表头
        sheet = createCellHead(clazz, sheet, 0);
        return sheet;
    }

    /**
     * 设置第一行列头的样式
     *
     * @param workbook 工作簿对象
     * @return 单元格样式对象
     */
    private static CellStyle buildHeadCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //对齐方式设置
        style.setAlignment(HorizontalAlignment.CENTER);
        //边框颜色和宽度设置
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
        //设置背景颜色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //粗体字设置
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    private static CellStyle buildSpecificCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 在指定的sheet和rowNum中填充表头
     *
     * @param clazz  用于生成表头的类对象
     * @param sheet  报表
     * @param rowNum 行号
     */
    private static Sheet createCellHead(Class clazz, Sheet sheet, int rowNum) {
        //获取表头
        List<String> CELL_HEADS = getCellHead(clazz);
        // 设置列头宽度
        for (int i = 0; i < CELL_HEADS.size(); i++) {
            sheet.setColumnWidth(i, 4500);
        }
        // 设置默认行高
        sheet.setDefaultRowHeight((short) 400);
        // 构建头单元格样式
        CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
        // 写入第一行各列的数据
        Row head = sheet.createRow(rowNum);
        for (int i = 0; i < CELL_HEADS.size(); i++) {
            Cell cell = head.createCell(i);
            cell.setCellValue(CELL_HEADS.get(i));
            cell.setCellStyle(cellStyle);
        }
        return sheet;
    }

    /**
     * 将数据转换成行
     *
     * @param data 源数据
     * @param row  行对象
     * @return
     */
    private static <T> void convertDataToRow(Workbook workbook, T data, Row row) {
        Cell cell;
        Class<?> clazz = data.getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            cell = row.createCell(i);
            fields[i].setAccessible(true);
            if (fields[i].isAnnotationPresent(CellIntegerConverter.class)) {
                Integer value = 0;
                try {
                    value = (Integer) fields[i].get(data);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                if (fields[i].isAnnotationPresent(CellStyleConverter.class) && value.equals(1)) {
                    cell.setCellValue("是");
                    cell.setCellStyle(buildSpecificCellStyle(workbook));
                } else if (value.equals(1)) {
                    cell.setCellValue("是");
                } else {
                    cell.setCellValue("否");
                }
            } else if (fields[i].isAnnotationPresent(CellDoubleConverter.class)) {
                try {
                    Double doubleVal = (Double) fields[i].get(data);
                    cell.setCellValue(doubleVal);
                    if (fields[i].isAnnotationPresent(CellStyleConverter.class) && doubleVal < 500) {
                        cell.setCellStyle(buildSpecificCellStyle(workbook));
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            } else {
                try {
                    Object o = fields[i].get(data);
                    if (o instanceof Long || o instanceof Integer || o instanceof Double) {
                        cell.setCellValue(String.valueOf(fields[i].get(data)));
                    } else {
                        //其他类型暂时强转成字符串
                        cell.setCellValue((String) fields[i].get(data));
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }


        if (data instanceof ChecklistOfCar) {

        } else if (data instanceof PushStatistic) {

        }
    }

    public static <T> List<T> excel2Collection(InputStream inputStream, Class<T> clazz, String fileName) {
        Map<Integer, String> cellIdxNameMap = new HashMap<>();
        Map<String, String> cellHeadFieldMap = covert2CellHeadFieldNameMap(clazz);
        List<T> res = new ArrayList<>();
        try {
            Workbook workbook;
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                throw new BadRequestException("fileName's suffix is invalid");
            }
            // 只读取第一张sheet
            int numberOfSheets = workbook.getNumberOfSheets();
            Sheet sheet = null;
            for (int n = 0; n < numberOfSheets; n++) {
                sheet = workbook.getSheetAt(n);
                if (ObjectUtils.isNotEmpty(sheet) && sheet.getSheetName().contains("大中修")) {
                    break;
                }
            }
            if (ObjectUtils.isEmpty(sheet)) {
                throw new BadRequestException("sheet name must contains '大中修'");
            }
            int rows = sheet.getPhysicalNumberOfRows();
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            DataFormatter dataFormatter = new DataFormatter();
            for (int i = 0; i < rows; i++) {
                // 标题行
                Row row = sheet.getRow(i);
                int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                if (i == 0) {
                    for (int j = 0; j < physicalNumberOfCells; j++) {
                        cellIdxNameMap.put(j, row.getCell(j).getStringCellValue());
                    }
                } else {
                    T instance = ReflectUtil.newInstance(clazz);
                    for (int j = 0; j < physicalNumberOfCells; j++) {
                        Cell cell = row.getCell(j);
                        if (ObjectUtils.isEmpty(cell)) {
                            continue;
                        }
                        CellType realCellType = cell.getCellType();
                        Object cellValue = null;
                        String cellHeadName = cellIdxNameMap.get(j);
                        if (StringUtils.isEmpty(cellHeadName)) {
                            continue;
                        }
                        String fieldName = cellHeadFieldMap.get(cellHeadName);
                        if (StringUtils.isEmpty(fieldName)) {
                            continue;
                        }
                        Field field = clazz.getDeclaredField(fieldName);
                        Excel excel = field.getAnnotation(Excel.class);
                        CellType expectedCellType = excel.type();
                        field.setAccessible(true);
                        // 根据excel的类型来决定值
                        System.out.println(realCellType);
                        switch (realCellType) {
                            case STRING:
                                String stringCellValue = cell.getStringCellValue();
                                if (expectedCellType.equals(CellType.STRING)) {
                                    cellValue = stringCellValue;
                                } else if (expectedCellType.equals(CellType.NUMERIC)) {
                                    if (StringUtils.isNumeric(stringCellValue)) {
                                        cellValue = Double.parseDouble(stringCellValue);
                                    } else {
                                        cellValue = DEFAULT_NUMERIC_VALUE;

                                    }
                                }
                                break;
                            case NUMERIC:
                                if (expectedCellType.equals(CellType.NUMERIC)) {
                                    System.out.println(cell.getNumericCellValue());
                                    cellValue = FormatUtil.keepDecimalsOfK(cell.getNumericCellValue(), 3);
                                }else if(expectedCellType.equals(CellType.FORMULA)){
                                    if(DateUtil.isCellDateFormatted(cell))
                                    {
                                        Date date = cell.getDateCellValue();
                                        cellValue =date.getTime();
                                    }
                                    else{
                                        cellValue = FormatUtil.keepDecimalsOfK(cell.getNumericCellValue(), 3);
                                    }

                                }
                                else {
                                    cellValue = DEFAULT_NUMERIC_VALUE;
                                }
                                break;
                            case FORMULA:
                                cellValue = dataFormatter.formatCellValue(cell, evaluator);
                                if (Objects.equal(expectedCellType, CellType.NUMERIC)) {
                                    try {
                                        cellValue = Double.parseDouble((String) cellValue);
                                    } catch (Exception e) {
                                        cellValue = DEFAULT_NUMERIC_VALUE;
                                    }
                                }
                                break;
                            case ERROR:
                                cellValue = null;
                                break;
                            case BLANK:
                                if (Objects.equal(expectedCellType, CellType.STRING)) {
                                    cellValue = DEFAULT_STRING_VALUE;
                                } else {
                                    cellValue = DEFAULT_NUMERIC_VALUE;
                                }
                                break;
                            default:
                        }
                        try {
                            field.set(instance, cellValue);
                        } catch (Exception e) {
                            log.error(i + "");
                        }
                    }
                    res.add(instance);
                }
            }
        } catch (IOException | NoSuchFieldException e) {
            e.printStackTrace();
        }
        return res;
    }

    public static <T> Map<String, String> covert2CellHeadFieldNameMap(Class<T> clazz) {
        Map<String, String> map = new HashMap<>();
        Arrays.stream(clazz.getDeclaredFields()).forEach(field -> {
            if (field.isAnnotationPresent(Excel.class)) {
                Excel annotation = field.getAnnotation(Excel.class);
                String cellHeadName = annotation.value();
                map.put(cellHeadName, field.getName());
            }
        });
        return map;
    }

    // 使用泛型支持导出任意类型的对象
    public static <T> byte[] exportListToExcel(List<T> dataList) throws IOException {
        if (dataList == null || dataList.isEmpty()) {
            return new byte[0]; // 返回空字节数组表示没有数据
        }

        // 创建一个新的工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建一个表格
        Sheet sheet = workbook.createSheet("Data");

        // 创建表头
        Row headerRow = sheet.createRow(0);
        Field[] fields = dataList.get(0).getClass().getDeclaredFields();

        // 生成表头，根据字段的 @Excel 注解
        int cellNum = 0;
        for (Field field : fields) {
            if (field.isAnnotationPresent(Excel.class)) {
                Excel excelAnnotation = field.getAnnotation(Excel.class);
                String header = excelAnnotation.value();
                headerRow.createCell(cellNum++).setCellValue(header);
            }
        }

        // 填充数据
        int rowNum = 1;
        for (T data : dataList) {
            Row row = sheet.createRow(rowNum++);
            cellNum = 0;
            for (Field field : fields) {
                if (field.isAnnotationPresent(Excel.class)) {
                    field.setAccessible(true);
                    try {
                        Method getterMethod = new PropertyDescriptor(field.getName(), data.getClass()).getReadMethod();
                        Object value = getterMethod.invoke(data);
                        row.createCell(cellNum++).setCellValue(value == null ? "" : value.toString());
                    } catch (Exception e) {
                        e.printStackTrace();
                        row.createCell(cellNum++).setCellValue("");
                    }
                }
            }
        }

        // 自动调整列宽
        for (int i = 0; i < fields.length; i++) {
            sheet.autoSizeColumn(i);
        }

        // 将工作簿写入到字节数组输出流中
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        workbook.close();
        return baos.toByteArray();
    }

}
